package com.bjsxt.shopping.product;

import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import com.bjsxt.shopping.category.Category;
import com.bjsxt.shopping.user.User;
import com.bjsxt.shopping.util.DB;

public class ProductMySQLDAO implements ProductDAO {

	public void add(Product p) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DB.getConn();
			String sql = "insert into product values (null, ?, ?, ?, ?, ?, ?)";
			pstmt = DB.prepare(conn, sql);
//			pstmt.setString(1, p.getName());
//			pstmt.setString(2, p.getDescr());
//			pstmt.setDouble(3, p.getNormalPrice());
//			pstmt.setDouble(4, p.getMemberPrice());
			
//			pstmt.setTimestamp(5, new Timestamp(p.getPdate().getTime()));
//			pstmt.setTime(5, new  java.sql.Time(p.getPdate().getTime()));
//			pstmt.setDate(5, (java.sql.Date) p.getPdate());
//			pstmt.setInt(6, p.getCategoryId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}

	public void delete(int id) {
		Connection conn = null;
		Statement stmt = null;
		String sql;
		try {
			conn = DB.getConn();
			sql = "delete from product where id = " + id;
			stmt = DB.getStatement(conn);
			DB.executeUpdate(stmt, sql);
		} finally {
			DB.close(stmt);
			DB.close(conn);
		}
	}

	public List<Product> getProducts() {
		Connection conn = DB.getConn();
		Statement stmt = DB.getStatement(conn);
		String sql = "select * from sendbox order by SAVE_TIME desc";
		ResultSet rs = DB.getResultSet(stmt, sql);
		List<Product> products = new ArrayList<Product>();
		try {
			while (rs.next()) {
				Product p = getProductFromRs(rs);
				products.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(stmt);
			DB.close(rs);
			DB.close(conn);
		}

		return products;
	}
	
	/**
	 * @param lazy Ϊtrueʱ��ֻȡProduct����Ϣ������ͬʱȡ��Product��Category�������Ϣ
	 */
	public int getProducts(List<Product> products, int pageNo, int pageSize, boolean lazy) {
		int totalRecords = -1;
		Connection conn = DB.getConn();
		Statement stmt = DB.getStatement(conn);
		
		String sql = "";
		if(lazy) {
			sql = "select * from product order by pdate desc";
		} else {
			sql = "select p.id productid, p.name pname, p.descr pdescr, p.normalprice, " +
					" p.memberprice, p.pdate, p.categoryid , " +
					" c.id categoryid, c.name cname, c.descr cdescr, c.pid, c.cno, c.grade " +
					" from product p join category c on (p.categoryid = c.id) order by p.pdate desc";
		}
		sql +=  " limit " + (pageNo - 1) * pageSize + "," + pageSize;
				
		
		ResultSet rs = DB.getResultSet(stmt, sql);
		
		Statement stmtCount = DB.getStatement(conn);
		ResultSet rsCount = DB.getResultSet(stmtCount,
				"select count(*) from product");
		
		//products = new ArrayList<Product>(); ǧ��С����仰��Ҫ���
		try {
			rsCount.next();
			totalRecords = rsCount.getInt(1);
			
			while (rs.next()) {
				Product p = null;
				if(lazy) {
					p = this.getProductFromRs(rs);
				} else {
					p = new Product();
					p.setId(rs.getInt("productid"));
//					p.setName(rs.getString("pname"));
//					p.setDescr(rs.getString("pdescr"));
//					p.setNormalPrice(rs.getDouble("normalprice"));
//					p.setMemberPrice(rs.getDouble("memberprice"));
//					p.setPdate(rs.getTimestamp("pdate"));
//					p.setCategoryId(rs.getInt("categoryid"));
					
					Category c = new Category();
					c.setId(rs.getInt("categoryid"));
					c.setName(rs.getString("cname"));
					c.setDescr(rs.getString("cdescr"));
					c.setPid(rs.getInt("pid"));
					c.setCno(rs.getInt("cno"));
					c.setGrade(rs.getInt("grade"));
					
//					p.setCategory(c);
				}
				
				
				products.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rsCount);
			DB.close(stmtCount);
			DB.close(stmt);
			DB.close(rs);
			DB.close(conn);
		}

		return totalRecords;
	}

	public Product loadById(int id) {
		Connection conn = null;
		ResultSet rs = null;
		Statement stmt = null;
		Product p = null;

		try {
			String sql = "select * from product where id = " + id;
			conn = DB.getConn();
			stmt = DB.getStatement(conn);
			rs = DB.getResultSet(stmt, sql);
			if (rs.next()) {
				p = getProductFromRs(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(stmt);
			DB.close(rs);
			DB.close(conn);
		}
		return p;
	}
	
	public Product loadWebById(int id) {
		Connection conn = null;
		ResultSet rs = null;
		Statement stmt = null;
		Product p = null;

		try {
			String sql = "select * from product where id = " + id;
			conn = DB.getConn();
			stmt = DB.getStatement(conn);
			rs = DB.getResultSet(stmt, sql);
			if (rs.next()) {
				p = getProductFromRs(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(stmt);
			DB.close(rs);
			DB.close(conn);
		}
		return p;
	}
	
	public Product loadTranslateById(int id) {
		Connection conn = null;
		ResultSet rs = null;
		Statement stmt = null;
		Product p = null;

		try {
			String sql = "select * from translate where id = " + id;
			conn = DB.getConn();
			stmt = DB.getStatement(conn);
			rs = DB.getResultSet(stmt, sql);
			if (rs.next()) {
				p = getProductFromRs(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(stmt);
			DB.close(rs);
			DB.close(conn);
		}
		return p;
	}
	
	public User loadUserById(String translatorid) {
		Connection conn = null;
		ResultSet rs = null;
		Statement stmt = null;
		User u = null;

		try {
			String sql = "select * from Userc123 where loginname = '" + translatorid +"' ";
			conn = DB.getConn();
			stmt = DB.getStatement(conn);
			rs = DB.getResultSet(stmt, sql);
			if (rs.next()) {
				u = getUserFromRs(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(stmt);
			DB.close(rs);
			DB.close(conn);
		}
		return u;
	}

	public void update(Product p) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DB.getConn();
			String sql = "update product set name=? , descr=?, normalprice=?, memberprice=?, categoryid=? where id=?";
			pstmt = DB.prepare(conn, sql);
//			pstmt.setString(1, p.getName());
//			pstmt.setString(2, p.getDescr());
//			pstmt.setDouble(3, p.getNormalPrice());
//			pstmt.setDouble(4, p.getMemberPrice());
//			pstmt.setInt(5, p.getCategoryId());
			pstmt.setInt(6, p.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}

	private Product getProductFromRs(ResultSet rs) {
		Product p = null;
		try {
			p = new Product();
			p.setId(rs.getInt("id"));
			p.setSubject(rs.getString("subject"));
			p.setContent(rs.getString("content"));
			p.setSave_time(rs.getString("save_time"));
			p.setTranslatedText(rs.getString("translatedtext"));
			p.setOpinion(rs.getString("opinion"));
//			p.setName(rs.getString("name"));
//			p.setDescr(rs.getString("descr"));
//			p.setNormalPrice(rs.getDouble("normalprice"));
//			p.setMemberPrice(rs.getDouble("memberprice"));
//			p.setPdate(rs.getTimestamp("pdate"));
//			p.setCategoryId(rs.getInt("categoryid"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return p;
	}
	
	private User getUserFromRs(ResultSet rs) {
		User u = null;
		try {
			u = new User();
			u.setId(rs.getInt("id"));
			u.setUsername(rs.getString("username"));
			u.setPhone(rs.getString("phone"));
			u.setTranslatorLevel(rs.getString("translatorLevel"));
			u.setLoginname(rs.getString("loginname"));
			u.setTranslates(rs.getInt("translates"));
			u.setPasstimes(rs.getInt("passtimes"));
//			u.setSubject(rs.getString("subject"));
//			p.setContent(rs.getString("content"));
//			p.setSave_time(rs.getString("save_time"));
//			p.setTranslatedText(rs.getString("translatedtext"));
//			p.setOpinion(rs.getString("opinion"));
//			p.setName(rs.getString("name"));
//			p.setDescr(rs.getString("descr"));
//			p.setNormalPrice(rs.getDouble("normalprice"));
//			p.setMemberPrice(rs.getDouble("memberprice"));
//			p.setPdate(rs.getTimestamp("pdate"));
//			p.setCategoryId(rs.getInt("categoryid"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return u;
	}

	public void delete(String conditionStr) {
		Connection conn = null;
		Statement stmt = null;
		String sql;
		try {
			conn = DB.getConn();
			sql = "delete from product " + conditionStr;
			stmt = DB.getStatement(conn);
			DB.executeUpdate(stmt, sql);
		} finally {
			DB.close(stmt);
			DB.close(conn);
		}
	}

	public int find(List<Product> products, int pageNo, int pageSize, String queryStr) {
		int totalRecords = -1;
		Connection conn = DB.getConn();
		Statement stmt = DB.getStatement(conn);
		
		String sql = "";
		sql = "select * from sendbox "+ queryStr +" order by SAVE_TIME desc ";
		
		sql +=  " limit " + (pageNo - 1) * pageSize + "," + pageSize;
		
		ResultSet rs = DB.getResultSet(stmt, sql);
		
		Statement stmtCount = DB.getStatement(conn);
		ResultSet rsCount = DB.getResultSet(stmtCount,
				"select count(*) from sendbox " + queryStr.replaceAll("p\\.", ""));
		
		try {
			rsCount.next();
			totalRecords = rsCount.getInt(1);
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//�������ڸ�ʽ
			
			while (rs.next()) {
				Product p = null;
				p = new Product();
				p.setId(rs.getInt("ID"));
				p.setSubject(rs.getString("SUBJECT"));
				String sDate =  rs.getDate("SAVE_TIME").toString()+" " +rs.getTime("SAVE_TIME").toString();
			
				p.setSave_time(sDate);
				p.setContent(rs.getString("content"));
				
//				p.setName(rs.getString("pname"));
//				p.setDescr(rs.getString("pdescr"));
//				p.setNormalPrice(rs.getDouble("normalprice"));
//				p.setMemberPrice(rs.getDouble("memberprice"));
//				p.setPdate(rs.getTimestamp("pdate"));
//				p.setCategoryId(rs.getInt("categoryid"));
				
				Category c = new Category();
//				c.setId(rs.getInt("categoryid"));
//				c.setName(rs.getString("cname"));
//				c.setDescr(rs.getString("cdescr"));
//				c.setPid(rs.getInt("pid"));
//				c.setCno(rs.getInt("cno"));
//				c.setGrade(rs.getInt("grade"));
				
//				p.setCategory(c);
				
				products.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			
		} finally {
			DB.close(rsCount);
			DB.close(stmtCount);
			DB.close(stmt);
			DB.close(rs);
			DB.close(conn);
		}

		return totalRecords;
	}
	
	public int findWebpage(List<Product> products, int pageNo, int pageSize, String queryStr) {
		int totalRecords = -1;
		Connection conn = DB.getConn();
		Statement stmt = DB.getStatement(conn);
		
		String sql = "";
		sql = "select * from product "+ queryStr +" order by SAVE_TIME ";
		
		sql +=  " limit " + (pageNo - 1) * pageSize + "," + pageSize;
		
		ResultSet rs = DB.getResultSet(stmt, sql);
		
		Statement stmtCount = DB.getStatement(conn);
		ResultSet rsCount = DB.getResultSet(stmtCount,
				"select count(*) from product " + queryStr.replaceAll("p\\.", ""));
		
		try {
			rsCount.next();
			totalRecords = rsCount.getInt(1);
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//�������ڸ�ʽ
			
			while (rs.next()) {
				Product p = null;
				p = new Product();
				p.setId(rs.getInt("ID"));
				p.setArticle(rs.getString("article"));
				p.setSubject(rs.getString("SUBJECT"));
				p.setPrice(rs.getDouble("price"));
				p.setWordsNum(rs.getInt("wordsNum"));
				
				String sDate =  rs.getDate("SAVE_TIME").toString()+" " +rs.getTime("SAVE_TIME").toString();
			
				p.setSave_time(sDate);
				p.setContent(rs.getString("content"));
				p.setArticle(rs.getString("article"));
				p.setUrgency(rs.getString("urgency"));
				p.setSend_status(rs.getString("send_status").toString());
				
//				p.setName(rs.getString("pname"));
//				p.setDescr(rs.getString("pdescr"));
//				p.setNormalPrice(rs.getDouble("normalprice"));
//				p.setMemberPrice(rs.getDouble("memberprice"));
//				p.setPdate(rs.getTimestamp("pdate"));
//				p.setCategoryId(rs.getInt("categoryid"));
				
				Category c = new Category();
//				c.setId(rs.getInt("categoryid"));
//				c.setName(rs.getString("cname"));
//				c.setDescr(rs.getString("cdescr"));
//				c.setPid(rs.getInt("pid"));
//				c.setCno(rs.getInt("cno"));
//				c.setGrade(rs.getInt("grade"));
				
//				p.setCategory(c);
				
				products.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			
		} finally {
			DB.close(rsCount);
			DB.close(stmtCount);
			DB.close(stmt);
			DB.close(rs);
			DB.close(conn);
		}

		return totalRecords;
	}
	
	public int findTransSelf(List<Product> products, int pageNo, int pageSize, String queryStr) {
		int totalRecords = -1;
		Connection conn = DB.getConn();
		Statement stmt = DB.getStatement(conn);
		
		String sql = "";
		sql = "select * from product "+ queryStr +" order by SAVE_TIME desc ";
		
		sql +=  " limit " + (pageNo - 1) * pageSize + "," + pageSize;
		
		ResultSet rs = DB.getResultSet(stmt, sql);
		
		Statement stmtCount = DB.getStatement(conn);
		ResultSet rsCount = DB.getResultSet(stmtCount,
				"select count(*) from product " + queryStr.replaceAll("p\\.", ""));
		
		try {
			rsCount.next();
			totalRecords = rsCount.getInt(1);
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//�������ڸ�ʽ
			
			while (rs.next()) {
				Product p = null;
				p = new Product();
				p.setId(rs.getInt("ID"));
				p.setArticle(rs.getString("article"));
				p.setSubject(rs.getString("SUBJECT"));
				p.setPrice(rs.getDouble("price"));
				p.setWordsNum(rs.getInt("wordsNum"));
				
				String sDate =  rs.getDate("SAVE_TIME").toString()+" " +rs.getTime("SAVE_TIME").toString();
				
				p.setSave_time(sDate);
				p.setContent(rs.getString("content"));
				p.setSend_status(rs.getString("send_status").toString());
				
				p.setArticle(rs.getString("article"));
				p.setSend_status(rs.getString("send_status"));
				p.setUrgency(rs.getString("urgency"));
//				p.setName(rs.getString("pname"));
//				p.setDescr(rs.getString("pdescr"));
//				p.setNormalPrice(rs.getDouble("normalprice"));
//				p.setMemberPrice(rs.getDouble("memberprice"));
//				p.setPdate(rs.getTimestamp("pdate"));
//				p.setCategoryId(rs.getInt("categoryid"));
				
				Category c = new Category();
//				c.setId(rs.getInt("categoryid"));
//				c.setName(rs.getString("cname"));
//				c.setDescr(rs.getString("cdescr"));
//				c.setPid(rs.getInt("pid"));
//				c.setCno(rs.getInt("cno"));
//				c.setGrade(rs.getInt("grade"));
				
//				p.setCategory(c);
				
				products.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			
		} finally {
			DB.close(rsCount);
			DB.close(stmtCount);
			DB.close(stmt);
			DB.close(rs);
			DB.close(conn);
		}

		return totalRecords;
	}
	
	public void submitTrans(Product p) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DB.getConn();
			String sql = "update product set translatedText=?,send_status=?,translatorid=?,times=? where id=?";
			pstmt = DB.prepare(conn, sql);
//			pstmt.setString(1, p.getName());
//			pstmt.setString(2, p.getDescr());
//			pstmt.setDouble(3, p.getNormalPrice());
//			pstmt.setDouble(4, p.getMemberPrice());
//			pstmt.setInt(5, p.getCategoryId());
			pstmt.setString(1, p.getTranslatedText());
			pstmt.setString(2, p.getSend_status());
			pstmt.setString(3, p.getTranslatorid());
			pstmt.setString(4, p.getTimes());
			pstmt.setInt(5, p.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}
	
	public void updateOrderStatus(Product p) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
		String sSaveTime =df.format(new Date());// new Date()为获取当前系统时间
		
		try {
			conn = DB.getConn();
			String sql = "update product set send_status=?,translatorid=?,grabTime=? where id=?";
			pstmt = DB.prepare(conn, sql);
			pstmt.setString(1, p.getSend_status());
			pstmt.setString(2, p.getTranslatorid());
			pstmt.setString(3, sSaveTime);
			pstmt.setInt(4, p.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}	
	
	public void submitTranSH(Product p) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DB.getConn();
			String sql = "update product set opinion=?,score=?,send_status=?,checkid=? where id=?";
			pstmt = DB.prepare(conn, sql);
//			pstmt.setString(1, p.getName());
//			pstmt.setString(2, p.getDescr());
//			pstmt.setDouble(3, p.getNormalPrice());
//			pstmt.setDouble(4, p.getMemberPrice());
//			pstmt.setInt(5, p.getCategoryId());
			pstmt.setString(1, p.getOpinion());
			pstmt.setString(2, p.getScore());
			pstmt.setString(3, p.getSend_status());
			pstmt.setString(4, p.getCheckid());
			pstmt.setInt(5, p.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}
	
	public void updateSendStatus(Product p) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DB.getConn();
			String sql = "update product set send_status=?  where id=?";
			pstmt = DB.prepare(conn, sql);
//			pstmt.setString(1, p.getName());
//			pstmt.setString(2, p.getDescr());
//			pstmt.setDouble(3, p.getNormalPrice());
//			pstmt.setDouble(4, p.getMemberPrice());
//			pstmt.setInt(5, p.getCategoryId());
			pstmt.setString(1, "翻译中");
			pstmt.setInt(2, p.getId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}
	
	public void addNew(Product p) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
			String sSaveTime =df.format(new Date());// new Date()为获取当前系统时间
			conn = DB.getConn();
			String sql = "insert into product(Subject, Content, Save_Time, Send_status, Price, Urgency, Article, WordsNum, consumerid) values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
			pstmt = DB.prepare(conn, sql);
			//pstmt.setInt(1, p.getId());
			pstmt.setString(1, p.getSubject());
			pstmt.setString(2, p.getContent());
			pstmt.setString(3, sSaveTime);
			pstmt.setString(4, p.getSend_status());
			pstmt.setDouble(5, p.getPrice());
			pstmt.setString(6, p.getUrgency());
			pstmt.setString(7, p.getArticle());
			pstmt.setInt(8, p.getWordsNum());
			pstmt.setString(9, p.getConsumerid());
			
//			pstmt.setTimestamp(5, new Timestamp(p.getPdate().getTime()));
//			pstmt.setTime(5, new  java.sql.Time(p.getPdate().getTime()));
//			pstmt.setDate(5, (java.sql.Date) p.getPdate());
//			pstmt.setInt(6, p.getCategoryId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}
	
	public void addpasstimes(User u) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			//SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
			//String sSaveTime =df.format(new Date());// new Date()为获取当前系统时间
			conn = DB.getConn();
			String sql = "update userc123 set paasstimes = ? where loginname = ?";
			pstmt = DB.prepare(conn, sql);
			//pstmt.setInt(1, p.getId());
			pstmt.setInt(1, u.getPasstimes()+1);
			
			pstmt.setString(2, u.getLoginname());
			
//			pstmt.setTimestamp(5, new Timestamp(p.getPdate().getTime()));
//			pstmt.setTime(5, new  java.sql.Time(p.getPdate().getTime()));
//			pstmt.setDate(5, (java.sql.Date) p.getPdate());
//			pstmt.setInt(6, p.getCategoryId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}
	
	public void addordertimes(User u) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			//SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
			//String sSaveTime =df.format(new Date());// new Date()为获取当前系统时间
			conn = DB.getConn();
			String sql = "update userc123 set translates = ? where loginname = ?";
			pstmt = DB.prepare(conn, sql);
			//pstmt.setInt(1, p.getId());
			pstmt.setInt(1, u.getTranslates()+1);
			
			pstmt.setString(2, u.getLoginname());
			
//			pstmt.setTimestamp(5, new Timestamp(p.getPdate().getTime()));
//			pstmt.setTime(5, new  java.sql.Time(p.getPdate().getTime()));
//			pstmt.setDate(5, (java.sql.Date) p.getPdate());
//			pstmt.setInt(6, p.getCategoryId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}
	
	public void resetTrans(Product p) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
			String sSaveTime =df.format(new Date());// new Date()为获取当前系统时间
			conn = DB.getConn();
			String sql = "update product set send_status=?,translatedText=?,consumerid=?,translatorid=?,opinion=?,score=?,checkid=?,times=?  where id=?";
			pstmt = DB.prepare(conn, sql);
			//pstmt.setInt(1, p.getId());
			pstmt.setString(1, "待抢单");
			pstmt.setString(2, "");
			pstmt.setString(3, "");
			pstmt.setString(4, "");
			pstmt.setString(5, "");
			pstmt.setInt(6, 0);
			pstmt.setString(7, "");
			pstmt.setString(8, "");
			pstmt.setInt(9, p.getId());
			
//			pstmt.setTimestamp(5, new Timestamp(p.getPdate().getTime()));
//			pstmt.setTime(5, new  java.sql.Time(p.getPdate().getTime()));
//			pstmt.setDate(5, (java.sql.Date) p.getPdate());
//			pstmt.setInt(6, p.getCategoryId());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}
	
	public int findtranstar(List<Translatorsanalysis> products, int pageNo, int pageSize, String queryStr) {
		int totalRecords = -1;
		Connection conn = DB.getConn();
		Statement stmt = DB.getStatement(conn);
		
		String sql = "";
		sql = "select * from userc123 a left join translatorsanalysis b on a.loginname = b.loginname "+ queryStr +" order by passtimes desc ";
		
		sql +=  " limit " + (pageNo - 1) * pageSize + "," + pageSize;
		
		ResultSet rs = DB.getResultSet(stmt, sql);
		
		Statement stmtCount = DB.getStatement(conn);
		ResultSet rsCount = DB.getResultSet(stmtCount,
				"select count(*) from userc123 " + queryStr.replaceAll("p\\.", ""));
		
		try {
			rsCount.next();
			totalRecords = rsCount.getInt(1);
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//�������ڸ�ʽ
			
			while (rs.next()) {
				Translatorsanalysis t = new Translatorsanalysis();
				t.setId(rs.getInt("ID"));
				t.setUsername(rs.getString("username"));
				t.setPhone(rs.getString("phone"));
				//String sDate =  rs.getDate("SAVE_TIME").toString()+" " +rs.getTime("SAVE_TIME").toString();
				t.setTranslatorLevel(rs.getString("translatorLevel"));
				
				
				t.setLoginname(rs.getString("loginname"));
				t.setPasstimes(rs.getInt("passtimes"));
				t.setTranslates(rs.getInt("translates"));
				
//				p.setName(rs.getString("pname"));
//				p.setDescr(rs.getString("pdescr"));
//				p.setNormalPrice(rs.getDouble("normalprice"));
//				p.setMemberPrice(rs.getDouble("memberprice"));
//				p.setPdate(rs.getTimestamp("pdate"));
//				p.setCategoryId(rs.getInt("categoryid"));
				
				Category c = new Category();
//				c.setId(rs.getInt("categoryid"));
//				c.setName(rs.getString("cname"));
//				c.setDescr(rs.getString("cdescr"));
//				c.setPid(rs.getInt("pid"));
//				c.setCno(rs.getInt("cno"));
//				c.setGrade(rs.getInt("grade"));
				
//				p.setCategory(c);
				
				products.add(t);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			
		} finally {
			DB.close(rsCount);
			DB.close(stmtCount);
			DB.close(stmt);
			DB.close(rs);
			DB.close(conn);
		}

		return totalRecords;
	}
}
